﻿using System;
using System.Data;
using System.Data.SqlClient;
namespace MDA
{

    /// <summary>
    /// 表T_USER数据访问类
    /// </summary>
    public class T_USERAccess
    {
        public SqlConnection connection = new SqlConnection(ConnectData.ConnectString);
        /// <summary>
        /// 获取将表数据插入数据库的命令对象(SqlCommand).将插入所有字段。
        /// </summary>
        /// <returns>返回一个插入命令对象(SqlCommand)</returns>
        public SqlCommand GetInsertCommand()
        {
            SqlCommand insert = new SqlCommand("insert into [T_USER]([PASSWORD],[STATE],[USERNAME],[ROLENAME],[USERID],[REGDATE],[TRUENAME],[REMARK],[DEPARTNAME],[HasPopedom])values(@PASSWORD,@STATE,@USERNAME,@ROLENAME,@USERID,@REGDATE,@TRUENAME,@REMARK,@DEPARTNAME,@HasPopedom)", connection);
            insert.Parameters.Add("@PASSWORD", SqlDbType.VarChar);
            insert.Parameters["@PASSWORD"].SourceColumn = "PASSWORD";
            insert.Parameters.Add("@STATE", SqlDbType.Int);
            insert.Parameters["@STATE"].SourceColumn = "STATE";
            insert.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            insert.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            insert.Parameters.Add("@ROLENAME", SqlDbType.VarChar);
            insert.Parameters["@ROLENAME"].SourceColumn = "ROLENAME";
            insert.Parameters.Add("@USERID", SqlDbType.VarChar);
            insert.Parameters["@USERID"].SourceColumn = "USERID";
            insert.Parameters.Add("@REGDATE", SqlDbType.DateTime);
            insert.Parameters["@REGDATE"].SourceColumn = "REGDATE";
            insert.Parameters.Add("@TRUENAME", SqlDbType.VarChar);
            insert.Parameters["@TRUENAME"].SourceColumn = "TRUENAME";
            insert.Parameters.Add("@REMARK", SqlDbType.VarChar);
            insert.Parameters["@REMARK"].SourceColumn = "REMARK";
            insert.Parameters.Add("@DEPARTNAME", SqlDbType.VarChar);
            insert.Parameters["@DEPARTNAME"].SourceColumn = "DEPARTNAME";
            insert.Parameters.Add("@HasPopedom", SqlDbType.Int);
            insert.Parameters["@HasPopedom"].SourceColumn = "HasPopedom";
            return insert;
        }
        /// <summary>
        /// 获取将表数据插入数据库的命令对象(SqlCommand)(除了自动增长键字段不插入，其它都插入。).用于有自动增长键的情况(自动增长键不需要插入.). 这个方法不同于GetInsertCommand().
        /// </summary>
        /// <returns>返回一个插入命令对象(SqlCommand)</returns>
        public SqlCommand GetInsertCommand_I()
        {
            SqlCommand insert = new SqlCommand("insert into [T_USER]([PASSWORD],[STATE],[USERNAME],[ROLENAME],[USERID],[REGDATE],[TRUENAME],[REMARK],[DEPARTNAME],[HasPopedom])values(@PASSWORD,@STATE,@USERNAME,@ROLENAME,@USERID,@REGDATE,@TRUENAME,@REMARK,@DEPARTNAME,@HasPopedom) set @return=@@identity", connection);
            insert.Parameters.Add("@PASSWORD", SqlDbType.VarChar);
            insert.Parameters["@PASSWORD"].SourceColumn = "PASSWORD";
            insert.Parameters.Add("@STATE", SqlDbType.Int);
            insert.Parameters["@STATE"].SourceColumn = "STATE";
            insert.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            insert.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            insert.Parameters.Add("@ROLENAME", SqlDbType.VarChar);
            insert.Parameters["@ROLENAME"].SourceColumn = "ROLENAME";
            insert.Parameters.Add("@USERID", SqlDbType.VarChar);
            insert.Parameters["@USERID"].SourceColumn = "USERID";
            insert.Parameters.Add("@REGDATE", SqlDbType.DateTime);
            insert.Parameters["@REGDATE"].SourceColumn = "REGDATE";
            insert.Parameters.Add("@TRUENAME", SqlDbType.VarChar);
            insert.Parameters["@TRUENAME"].SourceColumn = "TRUENAME";
            insert.Parameters.Add("@REMARK", SqlDbType.VarChar);
            insert.Parameters["@REMARK"].SourceColumn = "REMARK";
            insert.Parameters.Add("@DEPARTNAME", SqlDbType.VarChar);
            insert.Parameters["@DEPARTNAME"].SourceColumn = "DEPARTNAME";
            insert.Parameters.Add("@HasPopedom", SqlDbType.Int);
            insert.Parameters["@HasPopedom"].SourceColumn = "HasPopedom";
            insert.Parameters.Add("@return", SqlDbType.Int);
            insert.Parameters["@return"].Direction = ParameterDirection.Output;
            return insert;
        }
        /// <summary>
        /// 获取一个更新数据表的命令对象。这个方法将更新主键字段和其它所有字段。
        /// </summary>
        /// <returns>返回一个更新命令对象(SqlCommand)</returns>
        public SqlCommand GetUpdateCommand_P()
        {
            SqlCommand update = new SqlCommand("update [T_USER] set [PASSWORD]=@PASSWORD,[STATE]=@STATE,[USERNAME]=@USERNAME,[ROLENAME]=@ROLENAME,[USERID]=@USERID,[REGDATE]=@REGDATE,[TRUENAME]=@TRUENAME,[REMARK]=@REMARK,[DEPARTNAME]=@DEPARTNAME,[HasPopedom]=@HasPopedom where  [USERNAME]=@original_USERNAME ", connection);
            update.Parameters.Add("@PASSWORD", SqlDbType.VarChar);
            update.Parameters["@PASSWORD"].SourceColumn = "PASSWORD";
            update.Parameters.Add("@STATE", SqlDbType.Int);
            update.Parameters["@STATE"].SourceColumn = "STATE";
            update.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            update.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            update.Parameters.Add("@original_USERNAME", SqlDbType.VarChar);
            update.Parameters["@original_USERNAME"].SourceColumn = "USERNAME";
            update.Parameters["@original_USERNAME"].SourceVersion = DataRowVersion.Original;
            update.Parameters.Add("@ROLENAME", SqlDbType.VarChar);
            update.Parameters["@ROLENAME"].SourceColumn = "ROLENAME";
            update.Parameters.Add("@USERID", SqlDbType.VarChar);
            update.Parameters["@USERID"].SourceColumn = "USERID";
            update.Parameters.Add("@REGDATE", SqlDbType.DateTime);
            update.Parameters["@REGDATE"].SourceColumn = "REGDATE";
            update.Parameters.Add("@TRUENAME", SqlDbType.VarChar);
            update.Parameters["@TRUENAME"].SourceColumn = "TRUENAME";
            update.Parameters.Add("@REMARK", SqlDbType.VarChar);
            update.Parameters["@REMARK"].SourceColumn = "REMARK";
            update.Parameters.Add("@DEPARTNAME", SqlDbType.VarChar);
            update.Parameters["@DEPARTNAME"].SourceColumn = "DEPARTNAME";
            update.Parameters.Add("@HasPopedom", SqlDbType.Int);
            update.Parameters["@HasPopedom"].SourceColumn = "HasPopedom";
            return update;
        }
        /// <summary>
        /// 获取一个更新数据表的命令对象。这个方法将不更新主键字段，其它字段全部更新。
        /// </summary>
        /// <returns>返回一个更新命令对象(SqlCommand)</returns>
        public SqlCommand GetUpdateCommand()
        {
            SqlCommand update = new SqlCommand("update [T_USER] set [PASSWORD]=@PASSWORD,[STATE]=@STATE,[ROLENAME]=@ROLENAME,[USERID]=@USERID,[REGDATE]=@REGDATE,[TRUENAME]=@TRUENAME,[REMARK]=@REMARK,[DEPARTNAME]=@DEPARTNAME,[HasPopedom]=@HasPopedom where  [USERNAME]=@USERNAME ", connection);
            update.Parameters.Add("@PASSWORD", SqlDbType.VarChar);
            update.Parameters["@PASSWORD"].SourceColumn = "PASSWORD";
            update.Parameters.Add("@STATE", SqlDbType.Int);
            update.Parameters["@STATE"].SourceColumn = "STATE";
            update.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            update.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            update.Parameters.Add("@ROLENAME", SqlDbType.VarChar);
            update.Parameters["@ROLENAME"].SourceColumn = "ROLENAME";
            update.Parameters.Add("@USERID", SqlDbType.VarChar);
            update.Parameters["@USERID"].SourceColumn = "USERID";
            update.Parameters.Add("@REGDATE", SqlDbType.DateTime);
            update.Parameters["@REGDATE"].SourceColumn = "REGDATE";
            update.Parameters.Add("@TRUENAME", SqlDbType.VarChar);
            update.Parameters["@TRUENAME"].SourceColumn = "TRUENAME";
            update.Parameters.Add("@REMARK", SqlDbType.VarChar);
            update.Parameters["@REMARK"].SourceColumn = "REMARK";
            update.Parameters.Add("@DEPARTNAME", SqlDbType.VarChar);
            update.Parameters["@DEPARTNAME"].SourceColumn = "DEPARTNAME";
            update.Parameters.Add("@HasPopedom", SqlDbType.Int);
            update.Parameters["@HasPopedom"].SourceColumn = "HasPopedom";
            return update;
        }
        /// <summary>
        /// 获取一个根据主键删除数据表的命令对象。
        /// </summary>
        /// <returns>返回一个删除命令对象(SqlCommand)</returns>
        public SqlCommand GetDeleteCommand()
        {
            SqlCommand delete = new SqlCommand("delete from [T_USER] where [USERNAME]=@USERNAME ", connection);
            delete.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            delete.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            return delete;
        }
        /// <summary>
        /// 获取一个查询一个表的所有数据的命令对象。
        /// </summary>
        /// <returns>返回一个查询命令对象(SqlCommand)</returns>
        public SqlCommand GetSelectCommand()
        {
            SqlCommand select = new SqlCommand("select * from [T_USER]", connection);
            return select;
        }
        /// <summary>
        /// 获取一个查询命令对象，将根据主键进行查询，只返回一条数据。
        /// </summary>
        /// <returns>返回一个查询命令对象(SqlCommand)</returns>
        public SqlCommand GetSelectCommandPrimary()
        {
            SqlCommand select = new SqlCommand("select * from [T_USER] where [USERNAME]=@USERNAME ", connection);
            select.Parameters.Add("@USERNAME", SqlDbType.VarChar);
            select.Parameters["@USERNAME"].SourceColumn = "USERNAME";
            return select;
        }
        /// <summary>
        /// 更新一个本地表(DataTable),包括增,删,改.修改时包括修改主键.
        /// </summary>
        /// <param name="dt">本地表.将提交给数据库服务器.</param>
        /// <returns>如果成功,返回true;否则返回false.</returns>
        public bool Update_P(DataTable dt)
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.InsertCommand = GetInsertCommand();
            sda.UpdateCommand = GetUpdateCommand_P();
            sda.DeleteCommand = GetDeleteCommand();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            sda.InsertCommand.Transaction = tran;
            sda.UpdateCommand.Transaction = tran;
            sda.DeleteCommand.Transaction = tran;
            try
            {
                sda.Update(dt);
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally { connection.Close(); }
        }
        /// <summary>
        /// 更新一个本地表(DataTable),包括增,删,改.修改时不包括修改主键.
        /// </summary>
        /// <param name="dt">本地表.将提交给数据库服务器.</param>
        /// <returns>如果成功,返回true;否则返回false.</returns>
        public bool Update(DataTable dt)
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.InsertCommand = GetInsertCommand();
            sda.UpdateCommand = GetUpdateCommand();
            sda.DeleteCommand = GetDeleteCommand();
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            sda.InsertCommand.Transaction = tran;
            sda.UpdateCommand.Transaction = tran;
            sda.DeleteCommand.Transaction = tran;
            try
            {
                sda.Update(dt);
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally { connection.Close(); }
        }
        /// <summary>
        /// 查询所有的数据。
        /// </summary>
        /// <returns>查询的结果。一个本地表（DataTable）</returns>
        public DataTable Select()
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from [T_USER]", connection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 查询一个实体，要求查询的实体主键已经被初始化，并被作为条件.查询后，其所有字段将被赋值。
        /// </summary>
        /// <param name="data">实体对象.主键应该已经被初始化。</param>
        /// <returns>如果成功,返回true;否则返回false.</returns>
        public bool Select(T_USERData data)
        {
            try
            {
                SqlCommand command = GetSelectCommandPrimary();
                command.Parameters["@USERNAME"].Value = data.USERNAME;
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    if (!reader.IsDBNull(reader.GetOrdinal("PASSWORD")))
                        data.PASSWORD = reader.GetValue(reader.GetOrdinal("PASSWORD")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("SEQ")))
                        data.SEQ = Int32.Parse(reader.GetValue(reader.GetOrdinal("SEQ")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("STATE")))
                        data.STATE = Int32.Parse(reader.GetValue(reader.GetOrdinal("STATE")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("ROLENAME")))
                        data.ROLENAME = reader.GetValue(reader.GetOrdinal("ROLENAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("USERID")))
                        data.USERID = reader.GetValue(reader.GetOrdinal("USERID")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("REGDATE")))
                        data.REGDATE = DateTime.Parse(reader.GetValue(reader.GetOrdinal("REGDATE")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("TRUENAME")))
                        data.TRUENAME = reader.GetValue(reader.GetOrdinal("TRUENAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("REMARK")))
                        data.REMARK = reader.GetValue(reader.GetOrdinal("REMARK")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("DEPARTNAME")))
                        data.DEPARTNAME = reader.GetValue(reader.GetOrdinal("DEPARTNAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("HasPopedom")))
                        data.HasPopedom = Int32.Parse(reader.GetValue(reader.GetOrdinal("HasPopedom")).ToString());
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            { connection.Close(); }
        }
        /// <summary>
        /// 根据条件查询一个实体.查询后，其所有字段将被赋值。
        /// </summary>
        /// <param name="data">实体对象.将被初始化后作为方法结果。</param>
        /// <param name="condition">查询条件,即where以后的语句.</param>
        /// <returns>如果成功,返回true;否则返回false.</returns>
        public bool Select(T_USERData data, string condition)
        {
            if (data == null)
                data = new T_USERData();
            SqlCommand command = new SqlCommand("select * from [T_USER] where " + condition, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(reader.GetOrdinal("PASSWORD")))
                        data.PASSWORD = reader.GetValue(reader.GetOrdinal("PASSWORD")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("SEQ")))
                        data.SEQ = Int32.Parse(reader.GetValue(reader.GetOrdinal("SEQ")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("STATE")))
                        data.STATE = Int32.Parse(reader.GetValue(reader.GetOrdinal("STATE")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("USERNAME")))
                        data.USERNAME = reader.GetValue(reader.GetOrdinal("USERNAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("ROLENAME")))
                        data.ROLENAME = reader.GetValue(reader.GetOrdinal("ROLENAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("USERID")))
                        data.USERID = reader.GetValue(reader.GetOrdinal("USERID")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("REGDATE")))
                        data.REGDATE = DateTime.Parse(reader.GetValue(reader.GetOrdinal("REGDATE")).ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("TRUENAME")))
                        data.TRUENAME = reader.GetValue(reader.GetOrdinal("TRUENAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("REMARK")))
                        data.REMARK = reader.GetValue(reader.GetOrdinal("REMARK")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("DEPARTNAME")))
                        data.DEPARTNAME = reader.GetValue(reader.GetOrdinal("DEPARTNAME")).ToString();
                    if (!reader.IsDBNull(reader.GetOrdinal("HasPopedom")))
                        data.HasPopedom = Int32.Parse(reader.GetValue(reader.GetOrdinal("HasPopedom")).ToString());
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 删除一个实体。这个实体的主键将被作为删除条件.其它字段将不被用. 
        /// </summary>
        /// <param name="data">实体对象.将从数据库删除。</param>
        public void Delete(T_USERData data)
        {
            SqlCommand command = GetDeleteCommand();
            command.Parameters["@USERNAME"].Value = data.USERNAME;
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 更新一个实体。 根据主键更新其它字段.仅不更新主键.
        /// </summary>
        /// <param name="data">实体对象.将从数据库更新。</param>
        public void Update(T_USERData data)
        {
            SqlCommand command = GetUpdateCommand();
            if (data.PASSWORD != null)
                command.Parameters["@PASSWORD"].Value = data.PASSWORD;
            else
                command.Parameters["@PASSWORD"].Value = DBNull.Value;
            command.Parameters["@STATE"].Value = data.STATE;
            if (data.USERNAME != null)
                command.Parameters["@USERNAME"].Value = data.USERNAME;
            else
                command.Parameters["@USERNAME"].Value = DBNull.Value;
            if (data.ROLENAME != null)
                command.Parameters["@ROLENAME"].Value = data.ROLENAME;
            else
                command.Parameters["@ROLENAME"].Value = DBNull.Value;
            if (data.USERID != null)
                command.Parameters["@USERID"].Value = data.USERID;
            else
                command.Parameters["@USERID"].Value = DBNull.Value;
            if (data.REGDATE.Year != 1)
                command.Parameters["@REGDATE"].Value = data.REGDATE;
            else
                command.Parameters["@REGDATE"].Value = DBNull.Value;
            if (data.TRUENAME != null)
                command.Parameters["@TRUENAME"].Value = data.TRUENAME;
            else
                command.Parameters["@TRUENAME"].Value = DBNull.Value;
            if (data.REMARK != null)
                command.Parameters["@REMARK"].Value = data.REMARK;
            else
                command.Parameters["@REMARK"].Value = DBNull.Value;
            if (data.DEPARTNAME != null)
                command.Parameters["@DEPARTNAME"].Value = data.DEPARTNAME;
            else
                command.Parameters["@DEPARTNAME"].Value = DBNull.Value;
            command.Parameters["@HasPopedom"].Value = data.HasPopedom;
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 更新一个实体。 根据主键更新全部字段.包括更新主键.
        /// </summary>
        /// <param name="data">实体对象.将被更新。其它参数作为主键条件。</param>
        public void Update_P(T_USERData data, String _USERNAME)
        {
            SqlCommand command = GetUpdateCommand_P();
            if (data.PASSWORD != null)
                command.Parameters["@PASSWORD"].Value = data.PASSWORD;
            else
                command.Parameters["@PASSWORD"].Value = DBNull.Value;
            command.Parameters["@STATE"].Value = data.STATE;
            if (data.USERNAME != null)
                command.Parameters["@USERNAME"].Value = data.USERNAME;
            else
                command.Parameters["@USERNAME"].Value = DBNull.Value;
            command.Parameters["@original_USERNAME"].Value = _USERNAME;
            if (data.ROLENAME != null)
                command.Parameters["@ROLENAME"].Value = data.ROLENAME;
            else
                command.Parameters["@ROLENAME"].Value = DBNull.Value;
            if (data.USERID != null)
                command.Parameters["@USERID"].Value = data.USERID;
            else
                command.Parameters["@USERID"].Value = DBNull.Value;
            if (data.REGDATE.Year != 1)
                command.Parameters["@REGDATE"].Value = data.REGDATE;
            else
                command.Parameters["@REGDATE"].Value = DBNull.Value;
            if (data.TRUENAME != null)
                command.Parameters["@TRUENAME"].Value = data.TRUENAME;
            else
                command.Parameters["@TRUENAME"].Value = DBNull.Value;
            if (data.REMARK != null)
                command.Parameters["@REMARK"].Value = data.REMARK;
            else
                command.Parameters["@REMARK"].Value = DBNull.Value;
            if (data.DEPARTNAME != null)
                command.Parameters["@DEPARTNAME"].Value = data.DEPARTNAME;
            else
                command.Parameters["@DEPARTNAME"].Value = DBNull.Value;
            command.Parameters["@HasPopedom"].Value = data.HasPopedom;
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 插入一个实体到数据库.
        /// </summary>
        /// <param name="data">实体对象.将被插入到数据库，插入包括所有字段。</param>
        public void Insert(T_USERData data)
        {
            SqlCommand command = GetInsertCommand();
            if (data.PASSWORD != null)
                command.Parameters["@PASSWORD"].Value = data.PASSWORD;
            else
                command.Parameters["@PASSWORD"].Value = DBNull.Value;
            command.Parameters["@STATE"].Value = data.STATE;
            if (data.USERNAME != null)
                command.Parameters["@USERNAME"].Value = data.USERNAME;
            else
                command.Parameters["@USERNAME"].Value = DBNull.Value;
            if (data.ROLENAME != null)
                command.Parameters["@ROLENAME"].Value = data.ROLENAME;
            else
                command.Parameters["@ROLENAME"].Value = DBNull.Value;
            if (data.USERID != null)
                command.Parameters["@USERID"].Value = data.USERID;
            else
                command.Parameters["@USERID"].Value = DBNull.Value;
            if (data.REGDATE.Year != 1)
                command.Parameters["@REGDATE"].Value = data.REGDATE;
            else
                command.Parameters["@REGDATE"].Value = DBNull.Value;
            if (data.TRUENAME != null)
                command.Parameters["@TRUENAME"].Value = data.TRUENAME;
            else
                command.Parameters["@TRUENAME"].Value = DBNull.Value;
            if (data.REMARK != null)
                command.Parameters["@REMARK"].Value = data.REMARK;
            else
                command.Parameters["@REMARK"].Value = DBNull.Value;
            if (data.DEPARTNAME != null)
                command.Parameters["@DEPARTNAME"].Value = data.DEPARTNAME;
            else
                command.Parameters["@DEPARTNAME"].Value = DBNull.Value;
            command.Parameters["@HasPopedom"].Value = data.HasPopedom;
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 插入一个实体。 用于带唯一标示键(identity)的情况. 唯一标示键不能插入.其它字段全部插入。
        /// </summary>
        /// <param name="data">实体对象.将被插入，并返回唯一标示键。</param>
        public void Insert_I(T_USERData data)
        {
            SqlCommand command = GetInsertCommand_I();
            if (data.PASSWORD != null)
                command.Parameters["@PASSWORD"].Value = data.PASSWORD;
            else
                command.Parameters["@PASSWORD"].Value = DBNull.Value;
            command.Parameters["@STATE"].Value = data.STATE;
            if (data.USERNAME != null)
                command.Parameters["@USERNAME"].Value = data.USERNAME;
            else
                command.Parameters["@USERNAME"].Value = DBNull.Value;
            if (data.ROLENAME != null)
                command.Parameters["@ROLENAME"].Value = data.ROLENAME;
            else
                command.Parameters["@ROLENAME"].Value = DBNull.Value;
            if (data.USERID != null)
                command.Parameters["@USERID"].Value = data.USERID;
            else
                command.Parameters["@USERID"].Value = DBNull.Value;
            if (data.REGDATE.Year != 1)
                command.Parameters["@REGDATE"].Value = data.REGDATE;
            else
                command.Parameters["@REGDATE"].Value = DBNull.Value;
            if (data.TRUENAME != null)
                command.Parameters["@TRUENAME"].Value = data.TRUENAME;
            else
                command.Parameters["@TRUENAME"].Value = DBNull.Value;
            if (data.REMARK != null)
                command.Parameters["@REMARK"].Value = data.REMARK;
            else
                command.Parameters["@REMARK"].Value = DBNull.Value;
            if (data.DEPARTNAME != null)
                command.Parameters["@DEPARTNAME"].Value = data.DEPARTNAME;
            else
                command.Parameters["@DEPARTNAME"].Value = DBNull.Value;
            command.Parameters["@HasPopedom"].Value = data.HasPopedom;
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
                data.SEQ = (int)command.Parameters["@return"].Value;
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 根据条件查询数据。
        /// </summary>
        /// <param name="expression">查询表达式</param>
        /// <returns>返回一个本地表(DataTable)</returns>
        public DataTable Select(string expression)
        {
            SqlDataAdapter sda = new SqlDataAdapter(expression, connection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 执行一条Sql语句,没有任何返回数据。
        /// </summary>
        /// <param name="expression">执行Sql表达式</param>
        public void Excute(string expression)
        {
            SqlCommand command = new SqlCommand(expression, connection);
            connection.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (System.Exception ex) { throw ex; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 执行一条Sql语句,并返回第一行第一列的数据。
        /// </summary>
        /// <param name="expression">需要执行的Sql语句。</param>
        /// <returns>返回一个本地表(DataTable)</returns>
        public object SelectScalar(string expression)
        {
            SqlCommand command = new SqlCommand(expression, connection);
            connection.Open();
            object o = command.ExecuteScalar();
            connection.Close();
            return o;
        }
        /// <summary>
        /// 获取一个表的结构。
        /// </summary>
        /// <returns>具有数据库表结构的本地空表(DataTable)</returns>
        public DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter("select top 0 * from [T_USER]", connection);
            sda.FillSchema(dt, SchemaType.Source);
            return dt;
        }
    }
}
